package com.lifeverse.repository;

import com.lifeverse.entity.LifeEntity;
import com.lifeverse.entity.LifeEntityGroup;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 生命体群体数据访问层
 */
@Repository
public interface LifeEntityGroupRepository extends JpaRepository<LifeEntityGroup, Long> {
    
    /**
     * 根据群体名称查找群体
     */
    Optional<LifeEntityGroup> findByGroupName(String groupName);
    
    /**
     * 根据群体类型查找群体
     */
    List<LifeEntityGroup> findByGroupType(LifeEntityGroup.GroupType groupType);
    
    /**
     * 根据群体状态查找群体
     */
    List<LifeEntityGroup> findByGroupStatus(LifeEntityGroup.GroupStatus groupStatus);
    
    /**
     * 查找活跃的群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.groupStatus = 'ACTIVE'")
    List<LifeEntityGroup> findActiveGroups();
    
    /**
     * 根据领导者查找群体
     */
    List<LifeEntityGroup> findByLeader(LifeEntity leader);
    
    /**
     * 根据成员数量范围查找群体
     */
    List<LifeEntityGroup> findByMemberCountBetween(Integer minCount, Integer maxCount);
    
    /**
     * 查找大型群体（成员数量大于指定值）
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.memberCount >= :minSize")
    List<LifeEntityGroup> findLargeGroups(@Param("minSize") Integer minSize);
    
    /**
     * 根据智慧评分范围查找群体
     */
    List<LifeEntityGroup> findByWisdomScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找高智慧群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.wisdomScore >= 80.0")
    List<LifeEntityGroup> findHighWisdomGroups();
    
    /**
     * 根据凝聚力评分范围查找群体
     */
    List<LifeEntityGroup> findByCohesionScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找高凝聚力群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.cohesionScore >= 80.0")
    List<LifeEntityGroup> findHighCohesionGroups();
    
    /**
     * 根据效率评分范围查找群体
     */
    List<LifeEntityGroup> findByEfficiencyScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找高效群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.efficiencyScore >= 80.0")
    List<LifeEntityGroup> findHighEfficiencyGroups();
    
    /**
     * 根据创新能力评分范围查找群体
     */
    List<LifeEntityGroup> findByInnovationScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找创新群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.innovationScore >= 80.0")
    List<LifeEntityGroup> findInnovativeGroups();
    
    /**
     * 根据协作能力评分范围查找群体
     */
    List<LifeEntityGroup> findByCollaborationScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找高协作能力群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.collaborationScore >= 80.0")
    List<LifeEntityGroup> findHighCollaborationGroups();
    
    /**
     * 根据学习能力评分范围查找群体
     */
    List<LifeEntityGroup> findByLearningScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找学习能力强的群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.learningScore >= 80.0")
    List<LifeEntityGroup> findHighLearningGroups();
    
    /**
     * 根据适应能力评分范围查找群体
     */
    List<LifeEntityGroup> findByAdaptabilityScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找适应能力强的群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.adaptabilityScore >= 80.0")
    List<LifeEntityGroup> findHighAdaptabilityGroups();
    
    /**
     * 根据稳定性评分范围查找群体
     */
    List<LifeEntityGroup> findByStabilityScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找稳定的群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.stabilityScore >= 80.0")
    List<LifeEntityGroup> findStableGroups();
    
    /**
     * 根据多样性指数范围查找群体
     */
    List<LifeEntityGroup> findByDiversityIndexBetween(BigDecimal minIndex, BigDecimal maxIndex);
    
    /**
     * 查找多样性高的群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.diversityIndex >= 80.0")
    List<LifeEntityGroup> findHighDiversityGroups();
    
    /**
     * 根据活跃度范围查找群体
     */
    List<LifeEntityGroup> findByActivityLevelBetween(BigDecimal minLevel, BigDecimal maxLevel);
    
    /**
     * 查找高活跃度群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.activityLevel >= 80.0")
    List<LifeEntityGroup> findHighActivityGroups();
    
    /**
     * 查找包含指定成员的群体
     */
    @Query("SELECT g FROM LifeEntityGroup g JOIN g.members m WHERE m = :member")
    List<LifeEntityGroup> findGroupsByMember(@Param("member") LifeEntity member);
    
    /**
     * 查找公开的群体
     */
    List<LifeEntityGroup> findByIsPublicTrue();
    
    /**
     * 查找允许自由加入的群体
     */
    List<LifeEntityGroup> findByAllowFreeJoinTrue();
    
    /**
     * 查找可以加入新成员的群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.groupStatus = 'ACTIVE' AND (g.maxMembers IS NULL OR g.memberCount < g.maxMembers)")
    List<LifeEntityGroup> findGroupsAcceptingNewMembers();
    
    /**
     * 根据创建时间范围查找群体
     */
    List<LifeEntityGroup> findByFormedAtBetween(LocalDateTime startTime, LocalDateTime endTime);
    
    /**
     * 查找最近创建的群体
     */
    List<LifeEntityGroup> findTop10ByOrderByFormedAtDesc();
    
    /**
     * 查找最近活动的群体
     */
    List<LifeEntityGroup> findTop10ByOrderByLastActivityAtDesc();
    
    /**
     * 查找长时间未活动的群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.lastActivityAt < :threshold AND g.groupStatus = 'ACTIVE'")
    List<LifeEntityGroup> findInactiveGroups(@Param("threshold") LocalDateTime threshold);
    
    /**
     * 查找健康的群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.wisdomScore >= 60.0 AND g.cohesionScore >= 60.0 AND g.memberCount >= g.minMembers")
    List<LifeEntityGroup> findHealthyGroups();
    
    /**
     * 查找需要关注的群体（评分低或成员不足）
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE (g.wisdomScore < 40.0 OR g.cohesionScore < 40.0 OR g.memberCount < g.minMembers) AND g.groupStatus = 'ACTIVE'")
    List<LifeEntityGroup> findGroupsNeedingAttention();
    
    /**
     * 根据标签查找群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.tags LIKE %:tag%")
    List<LifeEntityGroup> findByTagsContaining(@Param("tag") String tag);
    
    /**
     * 统计群体数量按类型
     */
    @Query("SELECT g.groupType, COUNT(g) FROM LifeEntityGroup g GROUP BY g.groupType")
    List<Object[]> countGroupsByType();
    
    /**
     * 统计群体数量按状态
     */
    @Query("SELECT g.groupStatus, COUNT(g) FROM LifeEntityGroup g GROUP BY g.groupStatus")
    List<Object[]> countGroupsByStatus();
    
    /**
     * 计算平均群体大小
     */
    @Query("SELECT AVG(g.memberCount) FROM LifeEntityGroup g WHERE g.groupStatus = 'ACTIVE'")
    Double calculateAverageGroupSize();
    
    /**
     * 计算平均智慧评分
     */
    @Query("SELECT AVG(g.wisdomScore) FROM LifeEntityGroup g WHERE g.groupStatus = 'ACTIVE'")
    BigDecimal calculateAverageWisdomScore();
    
    /**
     * 分页查询群体
     */
    Page<LifeEntityGroup> findByGroupStatus(LifeEntityGroup.GroupStatus status, Pageable pageable);
    
    /**
     * 分页查询群体按类型
     */
    Page<LifeEntityGroup> findByGroupType(LifeEntityGroup.GroupType type, Pageable pageable);
    
    /**
     * 查找综合评分最高的群体
     */
    @Query("SELECT g FROM LifeEntityGroup g WHERE g.groupStatus = 'ACTIVE' ORDER BY (g.wisdomScore + g.cohesionScore + g.efficiencyScore + g.innovationScore + g.collaborationScore + g.learningScore + g.adaptabilityScore + g.stabilityScore) / 8 DESC")
    List<LifeEntityGroup> findTopPerformingGroups(Pageable pageable);
}